package dao.Impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.ArrayList;
import java.sql.Date;

import dao.IRoomDao;
import dao.IStudentDao;
import db.DbConnection;
import po.Student;

public class StudentDaoImpl implements IStudentDao {
	private Connection conn = null;
	@Override
	public void save(Student student) {
		
		conn = DbConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql ="insert into student values(?,?,?,?,?,?,?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, student.getStudentNo());
			ps.setString(2, student.getStudentName());
			ps.setDate(3, new java.sql.Date(student.getBirthday().getTime()));
			ps.setString(4, student.getTel());
			ps.setString(5, student.getEmail());
			ps.setInt(6, student.getRoom().getRoomNo());
			ps.setString(7, student.getNPlace());
			ps.setString(8, student.getAddress());
			ps.execute();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DbConnection.close(null, ps);
		}
		
	}

	@Override
	public void update(Student student) {
		conn = DbConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			String sql = "Update student SET STUDENT_NAME=?,BIRTHDAY=?,TEL=?,EMAIL=?,ROOM_NO=?,NPLACE=?,ADDRESS=? WHERE STUDENT_NO = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, student.getStudentName());
			ps.setDate(2, new java.sql.Date(student.getBirthday().getTime()));
			ps.setString(3, student.getTel());
			ps.setString(4, student.getEmail());
			ps.setInt(5, student.getRoom().getRoomNo());
			ps.setString(6, student.getNPlace());
			ps.setString(7, student.getAddress());
			ps.setLong(8, student.getStudentNo());
			ps.execute();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DbConnection.close(null, ps);
		}
	}

	@Override
	public void delete(Long studentNo) {
		conn = DbConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			String sql = "DELETE FROM student WHERE STUDENT_NO =?";
			ps = conn.prepareStatement(sql);
			ps.setLong(1, studentNo);
			ps.execute();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DbConnection.close(null, ps);
		}
		
	}
		
	

	@Override
	public Student get(Long studentNo) {
		Student student = new Student();
		IRoomDao roomDao = new RoomDaoImpl();
		conn = DbConnection.getConnection();
		List<Student> studentList = new ArrayList<Student>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT * FROM student WHERE STUDENT_NO =?";
			ps= conn.prepareStatement(sql);
			ps.setLong(1, studentNo);
			rs = ps.executeQuery();
			while(rs.next()) {
				student.setStudentNo(rs.getLong(1));
				student.setStudentName(rs.getString(2));
				student.setBirthday(rs.getDate(3));
				student.setTel(rs.getString(4));
				student.setEmail(rs.getString(5));
				student.setRoom(roomDao.get(rs.getInt(6)));
				student.setNPlace(rs.getString(7));
				student.setAddress(rs.getString(8));
				studentList.add(student);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DbConnection.close(null, ps);
		}
		return student;
	}

	@Override
	public List<Student> findAll() {
		
		IRoomDao roomDao = new RoomDaoImpl();
		List<Student> studentList = new ArrayList<Student>();
		conn = DbConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT * FROM student";
			ps= conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Student student = new Student();
				student.setStudentNo(rs.getLong(1));
				student.setStudentName(rs.getString(2));
				student.setBirthday(rs.getDate(3));
				student.setTel(rs.getString(4));
				student.setEmail(rs.getString(5));
				student.setRoom(roomDao.get(rs.getInt(6)));
				student.setNPlace(rs.getString(7));
				student.setAddress(rs.getString(8));
				studentList.add(student);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DbConnection.close(null, ps);
		}
		return studentList;
		
	}

	

}
